package com.itown.expo.service;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.time.DateUtils;

import webframe.common.dao.BaseUser;

import com.itown.expo.model.Container;
import com.itown.expo.model.ContainerCheck;
import com.itown.expo.model.Decl;
import com.itown.expo.model.DeclPrep;
import com.itown.expo.model.Goods;
import com.itown.expo.model.GoodsCheck;
import com.itown.expo.model.GoodsClearItem;
import com.itown.expo.model.GoodsPrep;
import com.itown.expo.model.GoodsSituation;
import com.itown.expo.model.InspResultSum;
import com.itown.expo.model.MainDeclGoods;
import com.itown.expo.model.MainGoodsDecl;
import com.itown.expo.model.NormalDeclGoods;
import com.itown.expo.model.NormalGoodsDecl;
import com.itown.expo.model.PassageBill;
import com.itown.expo.model.ReleaseDecl;
import com.itown.expo.model.ReleaseGoods;
import com.itown.expo.model.SupervisePlan;
import com.itown.expo.model.UnvalidDecl;
import com.itown.expo.model.WoodPackCheck;

public class CheckBizService extends MyBaseService {

	/** ************************main decl******************************* */
	// 查找展品类报检单 by Id
	public MainGoodsDecl findMainGoodsDeclById(Long id) {
		MainGoodsDecl decl = getMainGoodsDeclDao().get(id);
		return decl;
	}

	// 查找展品类货物 by declNO
	public List<MainDeclGoods> findMainDeclGoodsByDeclNo(String declNo) {
		List<MainDeclGoods> list = getMainDeclGoodsDao()
				.findByHql(
						"from MainDeclGoods t where t.declNo=? order by t.anchor desc, t.goodsNo asc",
						new Object[] { declNo });
		return list;
	}

	// 查找展品类货物 by goodsId
	public MainDeclGoods findMainDeclGoodsByGoodsId(Long goodsId) {
		return getMainDeclGoodsDao().get(goodsId);
	}

	// 查找需要核销的货物(包含核销项,通关单) by declNO
	public List<MainDeclGoods> findClearGoodsByDeclNo(String declNo) {
		List<MainDeclGoods> goodsList = getMainDeclGoodsDao()
				.findByHql(
						"from MainDeclGoods t where t.declNo=?  and t.anchor=1 order by t.goodsNo asc",
						new Object[] { declNo });
		List<GoodsClearItem> clearList = findGoodsClearItemListByDeclNo(declNo);
		for (MainDeclGoods goods : goodsList) {
			GoodsClearItem goodsClear = null;
			for (GoodsClearItem clear : clearList) {
				if (clear.getGoodsNo().equals(goods.getGoodsNo())) {
					goodsClear = clear;
				}
			}
			if (goodsClear == null) {
				goodsClear = new GoodsClearItem();
				goodsClear.setClearFlag(1L);
				goodsClear.setPassageBills(Collections.EMPTY_LIST);

			} else {
				List<PassageBill> passageBills = getPassageBillDao().findByHql(
						"from PassageBill t where t.declNo=? and t.goodsNo=?",
						new Object[] { goodsClear.getDeclNo(),
								goodsClear.getGoodsNo() });
				goodsClear.setPassageBills(passageBills);
				// goods.setClearFlag(goodsClear.getClearFlag()); //nouse
			}
			goods.setGoodsClearItem(goodsClear);
		}

		return goodsList;
	}

	// 查找核销项列表 by declNO
	public List<GoodsClearItem> findGoodsClearItemListByDeclNo(String declNo) {
		List<GoodsClearItem> list = getGoodsClearItemDao()
				.findByHql(
						"from GoodsClearItem t where t.declNo=? order by t.goodsNo asc",
						new Object[] { declNo });
		return list;
	}

	// ciq更新核销表
	public void updateCiqGoodsClear(Long declId, Long goodsId,
			GoodsClearItem item) {
		// 更新核销表
		GoodsClearItem saved = getGoodsClearItemDao().findSingleByHql(
				"from GoodsClearItem t where t.declNo=? and goodsNo=?",
				new Object[] { item.getDeclNo(), item.getGoodsNo() });
		saved.setBuyCount(item.getBuyCount());
		saved.setConsumeCount(item.getConsumeCount());
		saved.setDestroyCount(item.getDestroyCount());
		saved.setGiveupCount(item.getGiveupCount());
		saved.setLargessCount(item.getLargessCount());
		saved.setOtherCount(item.getOtherCount());
		saved.setReturnCount(item.getReturnCount());
		saved.setTransportCount(item.getTransportCount());
		saved.setBalance(item.getBalance());
		saved.setClearOption(item.getClearOption());
		saved.setAuditOpinion(item.getAuditOpinion());
		saved.setPunishCount(item.getPunishCount());
		saved.setLoseCount(item.getLoseCount());
		saved.setUpdateTime(new Date());

		if (saved.getAuditCount() == null) {
			saved.setAuditCount(0L);
		}
		saved.setAuditCount((saved.getAuditCount()) + 1l);
		saved.setClearFlag(item.getClearFlag());
		getGoodsClearItemDao().update(saved);
		item = saved;
		// other
		updateDeclAndGoodsByClear(item);
	}

	// 企业提交核销
	public void updateEntGoodsClear(Long declId, Long goodsId,
			GoodsClearItem item, List<PassageBill> passageList) {
		// 更新核销表
		GoodsClearItem saved = getGoodsClearItemDao().findSingleByHql(
				"from GoodsClearItem t where t.declNo=? and goodsNo=?",
				new Object[] { item.getDeclNo(), item.getGoodsNo() });
		if (saved != null) {
			saved.setBuyCount(item.getBuyCount());
			saved.setConsumeCount(item.getConsumeCount());
			saved.setDestroyCount(item.getDestroyCount());
			saved.setGiveupCount(item.getGiveupCount());
			saved.setLargessCount(item.getLargessCount());
			saved.setOtherCount(item.getOtherCount());
			saved.setReturnCount(item.getReturnCount());
			saved.setTransportCount(item.getTransportCount());
			saved.setPunishCount(item.getPunishCount());
			saved.setLoseCount(item.getLoseCount());
			saved.setBalance(item.getBalance());
			saved.setClearOption(item.getClearOption());

			saved.setEntOpinion(item.getEntOpinion());
			saved.setDeclareNo(item.getDeclareNo());
			saved.setClearFlag(item.getClearFlag());
			saved.setUpdateTime(new Date());

			getGoodsClearItemDao().update(saved);
			item = saved;
		} else {
			item.setUpdateTime(new Date());
			getGoodsClearItemDao().save(item);
		}

		// 更新通关单表
		List<PassageBill> oldList = getPassageBillDao().findByHql(
				"from PassageBill t where t.declNo=? and t.goodsNo=?",
				new Object[] { item.getDeclNo(), item.getGoodsNo() });
		for (PassageBill passage : oldList) {
			getPassageBillDao().delete(passage);
		}
		for (PassageBill passage : passageList) {
			getPassageBillDao().save(passage);
		}

		// other
		updateDeclAndGoodsByClear(item);
	}

	// 自动更新 Decl 和 Goods 表
	private void updateDeclAndGoodsByClear(GoodsClearItem clear) {
		// 更新goods表 //nouse
		// MainDeclGoods goods = (MainDeclGoods)
		// findGoodsByDeclNoAndGoodsNo(clear
		// .getDeclNo(), clear.getGoodsNo());
		// goods.setClearFlag(clear.getClearFlag());
		// getMainDeclGoodsDao().update(goods);

		// /// 更新decl表
		MainGoodsDecl decl = (MainGoodsDecl) findDeclByDeclNo(clear.getDeclNo());
		// 需要核销货物数量
		long c1 = getMainDeclGoodsDao()
				.findCountByHql(
						"select count(t.id) from MainDeclGoods t where t.anchor=? and t.declNo=?",
						new Object[] { 1L, decl.getDeclNo() });
		// 已核销货物数量
		long c2 = getGoodsClearItemDao()
				.findCountByHql(
						"select count(t.id) from GoodsClearItem t where t.clearFlag=? and t.declNo=?",
						new Object[] { 5L, decl.getDeclNo() });
		if (c1 == c2) {
			// 此单是否完成核销
			decl.setCellation(2L);
		}
		// 此单新核销申请的数量
		long c3 = getGoodsClearItemDao()
				.findCountByHql(
						"select count(t.id) from GoodsClearItem t where t.clearFlag=? and t.declNo=?",
						new Object[] { 4L, decl.getDeclNo() });
		decl.setEntClearApply(c3);
		getMainGoodsDeclDao().update(decl);

	}

	/** ************************normal decl******************************* */
	// 查找普货类报检单 by Id
	public NormalGoodsDecl findNormalGoodsDeclById(Long id) {
		return getNormalGoodsDeclDao().get(id);
	}

	// 查找普货类货物 by declNO
	public List<NormalDeclGoods> findNormalDeclGoodsByDeclNo(String declNo) {
		List<NormalDeclGoods> list = getNormalDeclGoodsDao()
				.findByHql(
						"from NormalDeclGoods t where t.declNo=? order by t.anchor desc,t.goodsNo asc",
						new Object[] { declNo });
		return list;
	}

	// 查找普货类货物 by goodsId
	public NormalDeclGoods findNormalDeclGoodsByGoodsId(Long goodsId) {
		return getNormalDeclGoodsDao().get(goodsId);
	}

	// 查找无效报检单(单个)
	public UnvalidDecl findSingleUnvalidDecl(String declNo) {
		return getUnvalidDeclDao().findSingleByHql(
				"from UnvalidDecl t where t.declNo like ?",
				new Object[] { declNo });
	}

	/** ************************common ******************************* */
	// 查找报检单 By Id
	public Decl findDeclById(Long id) {
		return getDeclDao().get(id);
	}

	// 查找报检号 By Date
	public List<String> findDeclNoByDate(Date beginDate, Date endDate) {
		if (endDate != null) {
			endDate = DateUtils.addDays(endDate, 1);
		}
		List<Decl> declList = findDeclByDate(beginDate, endDate);
		List<String> declNoList = new ArrayList<String>();
		for (Decl decl : declList) {
			declNoList.add(decl.getDeclNo());
		}
		return declNoList;
	}

	// 查找报检单 By Date
	public List<Decl> findDeclByDate(Date beginDate, Date endDate) {
		if (beginDate == null) {
			beginDate = new Date(0);
		}
		if (endDate == null) {
			endDate = new Date();
		}
		List<Decl> list = getDeclDao().findByHql(
				"from Decl t where t.declDate between ? and ?",
				new Object[] { beginDate, endDate });
		return list;
	}

	// 查找待审报检单 By declNo
	public DeclPrep findDeclPrepByDeclNo(String declNo) {
		return getDeclPrepDao().findSingleByHql(
				"from DeclPrep t where t.declNo=?", new Object[] { declNo });
	}

	// 查找待审货物list by declNO
	public List<GoodsPrep> findGoodsPrepByDeclNo(String declNo) {
		return getGoodsPrepDao().findByHql(
				"from GoodsPrep t where t.declNo=? order by t.goodsNo asc",
				new Object[] { declNo });
	}

	// 查找报检单 By declNo
	public Decl findDeclByDeclNo(String declNo) {
		return getDeclDao().findSingleByHql("from Decl t where t.declNo=?",
				new Object[] { declNo });
	}

	// 查找货物 by goodsId
	public Goods findGoodsByGoodsId(Long goodsId) {
		return getGoodsDao().get(goodsId);
	}

	// 查找货物 by declNO and goodsNo
	public Goods findGoodsByDeclNoAndGoodsNo(String declNo, Long goodsNo) {
		return getGoodsDao().findSingleByHql(
				"from Goods t where t.declNo=? and t.goodsNo=?",
				new Object[] { declNo, goodsNo });
	}

	// 查找货物list by declNO
	public List<Goods> findGoodsByDeclNo(String declNo) {
		return getGoodsDao()
				.findByHql(
						"from Goods t where t.declNo=? order by t.anchor desc, t.goodsNo asc",
						new Object[] { declNo });
	}

	// 获取集装箱信息
	public List<Container> findContainerByDecl(String declNo) {
		return getContainerDao().findByHql("from Container t where t.declNo=?",
				new Object[] { declNo });
	}

	// 获取报检单详细查验结果
	public InspResultSum findInspResultSumByDecl(String declNo) {
		return getInspResultSumDao().findSingleByHql(
				"from InspResultSum t where t.declNo=?",
				new Object[] { declNo });
	}

	// 获取集装箱查验结果
	public List<ContainerCheck> findContainerCheckByDeclNo(String declNo) {
		return getContainerCheckDao().findByHql(
				"from ContainerCheck t where t.declNo=?",
				new Object[] { declNo });
	}

	// 获取货物详细查验信息
	public GoodsCheck findGoodsCheckByDeclNoGoodsNo(String declNo, Long goodsNo) {
		return getGoodsCheckDao().findSingleByHql(
				"from GoodsCheck t where t.declNo=? and t.goodsNo=?",
				new Object[] { declNo, goodsNo });
	}

	// 获取木包装查验结果
	public List<WoodPackCheck> findWoodPackCheckByDeclNoGoodsNo(String declNo,
			Long goodsNo) {
		return getWoodPackCheckDao().findByHql(
				"from WoodPackCheck t where t.declNo=? and t.goodsNo=?",
				new Object[] { declNo, goodsNo });
	}

	// 获取木包装查验结果综合
	public WoodPackCheck findWoodPackCheckByDeclNoGoodsNoSynth(String declNo,
			Long goodsNo) {
		WoodPackCheck result = new WoodPackCheck();
		List<WoodPackCheck> list = findWoodPackCheckByDeclNoGoodsNo(declNo,
				goodsNo);
		if (goodsNo.intValue() == 1) {
			list.addAll(findWoodPackCheckByDeclNoGoodsNo(declNo, 0L));
		}
		if (list == null || list.size() == 0) {
			return result;
		} else {
			result.setWoodResultCode(list.get(0).getWoodResultCode());
			for (WoodPackCheck check : list) {
				if (!check.getWoodResultCode().equals("1")) {
					result.setWoodCauseCode(check.getWoodCauseCode());
					break;
				}
			}
			return result;
		}
	}

	// 获取货物、木包装的疫情信息(goodsNo!=0 inspObject=1/2)
	public List<GoodsSituation> findGoodsSituationByDeclNoGoodsNo(
			String declNo, Long goodsNo) {
		return getGoodsSituationDao().findByHql(
				"from GoodsSituation t where t.declNo=? and t.goodsNo=? ",
				new Object[] { declNo, goodsNo });
	}

	// 获取集装箱的疫情信息(goodsNo==0 inspObject==4)
	public List<GoodsSituation> findContainerSituationByDeclNo(String declNo) {
		return getGoodsSituationDao().findByHql(
				"from GoodsSituation t where t.declNo=? and t.inspObject=? ",
				new Object[] { declNo, "4" });
	}

	// 查找监管结果
	public SupervisePlan findSupervisePlanByDeclNo(String declNo) {
		SupervisePlan supervisePlan = getSupervisePlanDao().findSingleByHql(
				"from SupervisePlan t where t.declNo=?",
				new Object[] { declNo });
		return supervisePlan;
	}

	// 更新报检单
	public Decl updateDecl(Decl decl) {
		return getDeclDao().update(decl);
	}

	// 设置报检单备案号
	public void updateDeclBackupCode(Long id, String newbackupCode) {
		Decl decl = findDeclById(id);
		List<Goods> goodslist = findGoodsByDeclNo(decl.getDeclNo());
		decl.setBackupCode(newbackupCode);
		getDeclDao().update(decl);
		for (Goods goods : goodslist) {
			goods.setBackupCode(newbackupCode);
			getGoodsDao().update(goods);
		}
	}

	// 设定无效单 //只删除Decl Goods表中的数据
	public void updateDeclToUnValid(Long id, BaseUser user) {
		Decl decl = findDeclById(id);
		List<Goods> goodslist = findGoodsByDeclNo(decl.getDeclNo());
		getDeclDao().delete(decl);
		for (Goods goods : goodslist) {
			getGoodsDao().delete(goods);
		}

		if (getUnvalidDeclDao().findSingleByHql(
				"from UnvalidDecl t where t.declNo=?",
				new Object[] { decl.getDeclNo() }) == null) {
			UnvalidDecl ud = new UnvalidDecl();
			ud.setDeclNo(decl.getDeclNo());
			ud.setTime(new Date());
			ud.setUsername(user.getUsername());
			getUnvalidDeclDao().save(ud);
		}
	}

	// 新建监管结果
	public SupervisePlan saveSupervisePlanByDeclNo(Decl decl) {
		SupervisePlan supervisePlan = new SupervisePlan();
		supervisePlan.setDeclNo(decl.getDeclNo());
		supervisePlan.setResult(0L);
		supervisePlan.setModifyFlag(1L);
		supervisePlan.setDecl(decl);
		getSupervisePlanDao().save(supervisePlan);
		return supervisePlan;
	}

	// 提交监管结果
	public void updateMainSupervisePlan(Long declId, SupervisePlan mod) {
		Decl decl = getDeclDao().get(declId);
		SupervisePlan has = findSupervisePlanByDeclNo(decl.getDeclNo());
		has.setResult(mod.getResult());
		has.setRemark(mod.getRemark());
		has.setModifyFlag(2L);
		has.setDecl(decl);
		getSupervisePlanDao().update(has);
	}

	// 重新设置无效单有效
	public void deleteUnvalidDecl(Long id) {
		getUnvalidDeclDao().delete(id);
	}

	// 存储过程更新Decl所有的查验结果
	public void updateDeclResult(Long id) {
		Decl decl = getDeclDao().get(id);
		Connection conn = null;
		CallableStatement cstmt = null;
		try {
			String procedure = "{call pr_up_main(?) }";
			conn = getConnection();
			cstmt = conn.prepareCall(procedure);
			cstmt.setString(1, decl.getDeclNo());
			cstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			releaseDBResource(conn);
		}
	}

	// 存储过程获取通关单详细数据
	public ReleaseDecl updateReleaseDecl(String declNo) {
		Connection conn = null;
		CallableStatement cstmt = null;
		ReleaseDecl releaseDecl = null;
		List<ReleaseGoods> goodsList = null;
		Long count = getReleaseDeclDao().findCountByHql(
				"select count(*) from ReleaseDecl t where t.declNo=?",
				new Object[] { declNo });
		if (count.intValue() == 0) {
			try {
				String procedure = "{call get_release(?) }";
				conn = getConnection();
				cstmt = conn.prepareCall(procedure);
				cstmt.setString(1, declNo);
				cstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				releaseDBResource(conn);
			}
		}
		releaseDecl = getReleaseDeclDao().findSingleByHql(
				"from ReleaseDecl t where t.declNo=?", new Object[] { declNo });
		if (releaseDecl != null) {
			goodsList = getReleaseGoodsDao().findByHql(
					"from ReleaseGoods t where t.declNo=? order by t.goodsNo",
					new Object[] { declNo });
			releaseDecl.setReleaseGoodsList(goodsList);
		}
		return releaseDecl;
	}

	// 存储过程手工获取报检单(待审)
	public void updateFetchDeclPrep(String declNo) {
		Connection conn = null;
		CallableStatement cstmt = null;
		try {
			String procedure = "{call get_decl_prep(?) }";
			conn = getConnection();
			cstmt = conn.prepareCall(procedure);
			cstmt.setString(1, declNo);
			cstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			releaseDBResource(conn);
		}
	}

	// 存储过程手工获取报检单及查验结果
	public void updateFetchDecl(Decl cdecl) {
		Connection conn = null;
		CallableStatement cstmt = null;
		try {
			String procedure = "{call get_decl(?,?,?) }";
			conn = getConnection();
			cstmt = conn.prepareCall(procedure);
			cstmt.setString(1, cdecl.getDeclNo());
			cstmt.setString(2, cdecl.getBackupCode());
			cstmt.setString(3, cdecl.getType());
			cstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			releaseDBResource(conn);
		}
	}

	// 查询Container中所有不同的集装箱规格
	public List<String> findDistinctContainerModel() {
		List list = getContainerDao().findByHql(
				"select distinct t.containerModelCode from Container t");
		return (List<String>) list;
	}

	

}
